-- exec sp_load_fg_lots_for_search '11', 'osep', 1
Alter proc sp_load_fg_lots_for_search
( 
	@tr_processStep_id int,
	@tr_vendor_name varchar(50),
	@Tape_Reel_Request_Id int
)
as
begin

	DECLARE @lot_id INT
	DECLARE @lot_number VARCHAR(50)
	DECLARE @qty_available INT
	DECLARE @qty_pulled INT
	DECLARE @fg_processStep_id INT --
	DECLARE @fg_location_id INT --
	DECLARE @tr_qty INT
	DECLARE @qty_return INT
	DECLARE @qty_reject INT
	DECLARE @Part_number VARCHAR(50)
	DECLARE @ROW_COUNT INT
	

	DECLARE @results TABLE ( Part_Number VARCHAR(50), lot_id INT, lot_number VARCHAR(50), qty_available INT, qty_pulled INT, tr_qty INT, qty_return INT, Qty_Rejected INT )

	select  @fg_processStep_id = PROCESSSTEP_ID from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@tr_processStep_id,0 ) WHERE PS_TYPE = 'FG' AND PROCESSSTEP_ID != @tr_processStep_id
	select  @fg_location_id = location_id from location where L_vendor_Name = @tr_vendor_name and L_TYpe = 'Finished Goods'

	IF( @fg_processStep_id <> -1  AND @fg_location_id <> -1 )
	BEGIN
		IF( @Tape_Reel_Request_Id = -1 )
		BEGIN
			INSERT INTO @results( Part_Number, lot_id, lot_number, qty_available, qty_pulled, tr_qty, qty_return, Qty_Rejected )
			(SELECT P.PART_NUMBER, LT.LOT_ID, LT.LOT_NUMBER, LT.QTY_IN, 0, 0, 0, 0 FROM LOT_TABLE LT, PRODUCT P
			 WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID = @fg_processStep_id AND LOCATION_ID = @fg_location_id AND LT.Lot_Current_Status != 'Close' )
		END
		ELSE
		BEGIN
			INSERT INTO @results( Part_Number, lot_id, lot_number, qty_available, qty_pulled, tr_qty, qty_return, Qty_Rejected )
			(SELECT P.PART_NUMBER, LT.LOT_ID, LT.LOT_NUMBER, LT.QTY_IN, 0, 0, 0, 0 FROM LOT_TABLE LT, PRODUCT P
			 WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID = @fg_processStep_id AND LOCATION_ID = @fg_location_id AND LT.Lot_Current_Status != 'Close' )
			
			DECLARE @tapereelrequestcursor CURSOR 
			SET @tapereelrequestcursor = CURSOR FOR SELECT P.Part_Number, Lt.Lot_Id, Lt.Lot_Number, TRRLT.Qty_Pulled - TRRLT.Return_Qty, TRRLT.Qty_Pulled, TRRLT.TR_Qty, TRRLT.Return_Qty, TRRLT.Reject_Qty FROM Tape_Reel_REQUEST_LOT_TABLE TRRLT, Lot_Table LT, Product P
			WHERE TRRLT.Lot_Id = Lt.Lot_ID and Lt.ProcessStep_ID = P.ProcessStep_Id AND TRRLT.Tape_Reel_Request_Id = @Tape_Reel_Request_Id

			OPEN @tapereelrequestcursor
			FETCH NEXT FROM @tapereelrequestcursor INTO @Part_number, @lot_id, @lot_number, @qty_available, @qty_pulled, @tr_qty, @qty_return, @qty_reject
			WHILE @@FETCH_STATUS = 0
	 		BEGIN
				
				SET @ROW_COUNT = 0
				SELECT @ROW_COUNT = COUNT(*) FROM @results WHERE Lot_Id = @lot_id
				IF @ROW_COUNT > 0
				BEGIN
					UPDATE @results SET qty_available = qty_available + @qty_available, qty_pulled = qty_pulled + @qty_pulled, tr_qty = tr_qty + @tr_qty, qty_return = qty_return + @qty_return, Qty_Rejected = @qty_reject WHERE LOT_ID = @lot_id
				END
				ELSE
				BEGIN
					INSERT INTO @results( Part_Number, lot_id, lot_number, qty_available, qty_pulled, tr_qty, qty_return, Qty_Rejected ) VALUES
					( @Part_number, @lot_id, @lot_number, @qty_available, @qty_pulled, @tr_qty, @qty_return, @qty_reject )	
				END
		
				FETCH NEXT FROM @tapereelrequestcursor INTO @Part_number, @lot_id, @lot_number, @qty_available, @qty_pulled, @tr_qty, @qty_return, @qty_reject
			END
			CLOSE @tapereelrequestcursor
			DEALLOCATE @tapereelrequestcursor
		END
	END	
	SELECT * FROM @RESULTS WHERE Qty_Available > 0 ORDER BY qty_pulled desc

end

